package com.example.teacherservice.repository;

import com.example.teacherservice.dto.StudentBasicDTO;
import com.example.teacherservice.entity.*;
import com.example.teacherservice.entity.Class;
import jakarta.transaction.Transactional;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface ClassRepository extends JpaRepository<Class, Long> {
    boolean existsByClassName(String className);

    @Query("SELECT c FROM Class c WHERE c.id = ?1 AND c.deleted = 0")
    Optional<Class> findActiveById(Long id);

    @Modifying
    @Transactional
    @Query("UPDATE Class c SET c.deleted = 1 WHERE c.id = ?1")
    void softDeleteById(Long id);

    // 修改这个方法，移除course属性路径
    @EntityGraph(attributePaths = {"students", "teachers", "couClasses"})
    @Query("SELECT c FROM Class c WHERE c.id = ?1 AND c.deleted = 0")
    Optional<Class> findActiveByIdWithDetails(Long id);

    // 修改这个方法，移除course属性路径
    @EntityGraph(attributePaths = {"students.user", "teachers", "couClasses"})
    @Query("SELECT c FROM Class c WHERE c.id = ?1 AND c.deleted = 0")
    Optional<Class> findWithDetailsById(Long id);

    @Query("SELECT t FROM Teacher t WHERE t.id IN " +
            "(SELECT tc.teaId FROM TeaClass tc WHERE tc.classId = :classId)")
    List<Teacher> findTeachersByClassId(@Param("classId") Long classId);

    // 修改查询方法，确保正确关联
    @Query("SELECT cc FROM CouClass cc JOIN FETCH cc.course WHERE cc.classInfo.id = :classId")
    List<CouClass> findCouClassesByClassId(@Param("classId") Long classId);


    @Query("SELECT cc FROM CouClass cc JOIN FETCH cc.course WHERE cc.course.id = :courseId")
    List<CouClass> findByCourseId(@Param("courseId") Long courseId);

    @Query("SELECT sc FROM StuClass sc WHERE sc.classId IN " +
            "(SELECT cc.classInfo.id FROM CouClass cc WHERE cc.course.id = :courseId)")
    List<StuClass> findStuClassesByCourseId(@Param("courseId") Long courseId);

    // 原生SQL备用方案
    @Query(value = "SELECT s.* FROM student s WHERE s.class_id = :classId",
            nativeQuery = true)
    List<Student> findStudentsByClassIdNative(@Param("classId") Long classId);

    // 通过中间表stu_class查询学生基本信息
    @Query("SELECT s FROM Student s " +
            "WHERE s.id IN (SELECT sc.stuId FROM StuClass sc WHERE sc.classId = :classId)")
    List<Student> findStudentsByClassIdThroughStuClass(@Param("classId") Long classId);

    // 通过中间表stu_class查询学生详细信息（带用户和班级信息）
    @Query("SELECT DISTINCT s FROM Student s " +
            "LEFT JOIN FETCH s.user " +
            "LEFT JOIN FETCH s.classInfo " +
            "WHERE s.id IN (SELECT sc.stuId FROM StuClass sc WHERE sc.classId = :classId)")
    List<Student> findStudentsWithDetailsByClassIdThroughStuClass(@Param("classId") Long classId);

    // 通过课程ID查询学生（先通过cou_class找到班级，再通过stu_class找到学生）
    @Query("SELECT s FROM Student s " +
            "WHERE s.id IN (" +
            "  SELECT sc.stuId FROM StuClass sc " +
            "  WHERE sc.classId IN (" +
            "    SELECT cc.classInfo.id FROM CouClass cc WHERE cc.course.id = :courseId" +
            "  )" +
            ")")
    List<Student> findStudentsByCourseId(@Param("courseId") Long courseId);

    // 通过课程ID查询学生详细信息（带用户和班级信息）
    @Query("SELECT DISTINCT s FROM Student s " +
            "LEFT JOIN FETCH s.user " +
            "LEFT JOIN FETCH s.classInfo " +
            "WHERE s.id IN (" +
            "  SELECT sc.stuId FROM StuClass sc " +
            "  WHERE sc.classId IN (" +
            "    SELECT cc.classInfo.id FROM CouClass cc WHERE cc.course.id = :courseId" +
            "  )" +
            ")")
    List<Student> findStudentsWithDetailsByCourseId(@Param("courseId") Long courseId);

    @Query("SELECT s FROM Student s " +
            "LEFT JOIN FETCH s.classInfo " +  // 明确初始化班级信息
            "WHERE s.id IN (SELECT sc.stuId FROM StuClass sc WHERE sc.classId = :classId)")
    List<Student> findStudentsWithClassByClassId(@Param("classId") Long classId);

    @Query("SELECT tc FROM TeaClass tc WHERE tc.teaId = :teaId")
    List<TeaClass> findByTeaId(@Param("teaId") Long teaId);

    @Query("SELECT tc FROM TeaClass tc JOIN Class c ON tc.classId = c.id " +
            "WHERE tc.teaId = :teaId AND c.className LIKE %:name%")
    Page<TeaClass> findByTeaIdAndClassNameContaining(
            @Param("teaId") Long teaId,
            @Param("name") String name,
            Pageable pageable);

    @Query("SELECT new com.example.teacherservice.dto.StudentBasicDTO(s.id, s.stuName, s.stuStudyTime, s.stuScore) " +
            "FROM Student s WHERE s.id IN " +
            "(SELECT sc.stuId FROM StuClass sc WHERE sc.classId = :classId)")
    List<StudentBasicDTO> findBasicStudentsByClassId(@Param("classId") Long classId);

    @Query("SELECT tc FROM TeaClass tc WHERE tc.teaId = :teaId")
    Page<TeaClass> findByTeaId(@Param("teaId") Long teaId, Pageable pageable);

    // 根据ID查询班级
    Optional<Class> findById(Long id);
}